考勤案例 CROSS JOIN

SELECT c.calendar_date, e.emp_name, a.clock_in, a.clock_out,
	CASE WHEN a.clock_out IS NULL THEN "缺勤"
		WHEN EXTRACT(HOUR FROM a.clock_in)>=9 THEN "迟到"
		ELSE "早退"
	END AS "考勤状态"
FROM calendar c
CROSS JOIN employee e
LEFT JOIN attendance a
ON (a.check_date = c.calendar_date AND a.emp_id = e.emp_id)
WHERE c.calendar_year = 2021 AND c.calendar_month =1 AND c.is_work_day = 'Y'
AND ( a.id IS NULL
	OR a.clock_out IS NULL
	OR EXTRACT(HOUR FROM a.clock_in)>=9
	OR EXTRACT(HOUR FROM a.clock_out)<18

);


-- 创建日历表calendar
CREATE TABLE calendar(
  id             INTEGER NOT NULL PRIMARY KEY, -- 日历编号
  calendar_date  DATE NOT NULL UNIQUE, -- 日历日期
  calendar_year  INTEGER NOT NULL, -- 日历年
  calendar_month INTEGER NOT NULL, -- 日历月
  calendar_day   INTEGER NOT NULL, -- 日历日
  is_work_day    VARCHAR(1) DEFAULT 'Y' NOT NULL -- 是否工作日
);

-- 创建考勤记录表attendance
-- Oracle、MySQL、PostgreSQL 以及 SQLite
CREATE TABLE attendance(
  id         INTEGER NOT NULL PRIMARY KEY, -- 考勤记录编号
  check_date DATE NOT NULL, -- 考勤日期
  emp_id     INTEGER NOT NULL, -- 员工编号
  clock_in   TIMESTAMP, -- 上班打卡时间
  clock_out  TIMESTAMP, -- 下班打卡时间
  CONSTRAINT uk_attendance UNIQUE (check_date, emp_id)
);

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `emp_id` int DEFAULT NULL,
  `emp_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (1, 'Chan Ho Yin');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (2, 'Connie Crawford');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (3, 'Jeff Lopez');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (4, 'Dong Xiaoming');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (5, 'Tang Ka Man');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (6, 'Xue Lan');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (7, 'Inoue Riku');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (8, 'Kathleen Cruz');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (9, 'Earl Guzman');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (10, 'Okada Hina');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (11, 'Yip Sze Kwan');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (12, 'Margaret Chavez');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (13, 'Zhu Xiaoming');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (14, 'Wu Wai Yee');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (15, 'Lam Wing Fat');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (16, 'Ota Sakura');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (17, 'Nakagawa Mitsuki');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (18, 'Wu Lu');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (19, 'Wei Zitao');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (20, 'Roy Dixon');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (21, 'Gong Zhiyuan');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (22, 'Matsui Ayato');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (23, 'Yamaguchi Kasumi');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (24, 'Han Wing Suen');
INSERT INTO `employee` (`emp_id`, `emp_name`) VALUES (25, 'Suzuki Ayano');